﻿Imports MySql.Data.MySqlClient 
Imports MySql.Data.Types 
Imports Microsoft.VisualBasic.Devices
Imports System.Xml

Public Class MySQLWallpaperService
  Inherits WallpaperService

  Public Sub New(ByVal connection As MySQLConnection)
    MyBase.New(connection)
  End Sub

  Public Overrides Sub IncrementWallpaperSet(ByRef value As Wallpaper)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String

    'sqls = "UPDATE skins SET s_set = s_set + 1 WHERE s_filename = '" & value.Filename & "'"
    sqls = "UPDATE skins SET s_set = ifnull(s_set,0) + 1 WHERE s_filename = @filename"

    cmd.CommandText = sqls
    cmd.Parameters.Add(New MySQLParameter("filename", MySQLDbType.VarChar)).Value = value.Filename
    cmd.ExecuteNonQuery()

    cmd.Dispose()

    value.Gesetzt += 1
  End Sub

  Public Overrides ReadOnly Property CheckOrgaIDexists(ByVal orga_id As Integer, ByVal resolution As String) As Boolean
    Get
      Dim cmd As MySQLCommand = _connection.CreateCommand()
      Dim da As MySQLDataAdapter
      Dim ds As New DataSet
      Dim sqls As String

      sqls = "SELECT case count(*) when 0 then 'false' else 'true' end exist "
      sqls &= vbNewLine & " FROM skins "
      sqls &= vbNewLine & " WHERE s_organr = @organr "
      sqls &= vbNewLine & "   AND s_resolution = @resolution "

      cmd.CommandText = sqls

      cmd.Parameters.Add(New MySQLParameter("organr", MySQLDbType.Int32)).Value = orga_id
      cmd.Parameters.Add(New MySQLParameter("resolution", MySQLDbType.VarChar)).Value = resolution

      da = New MySQLDataAdapter(cmd)
      da.Fill(ds)

      If ds.Tables(0).Rows.Count > 0 Then
        Return Boolean.Parse(ds.Tables(0).Rows(0).Item("exist"))
      Else
        cmd.Dispose()
        Return False
      End If

    End Get
  End Property

  Public Overrides ReadOnly Property GetWallpaperById(ByVal wallpaper_id As Integer) As Wallpaper
    Get
      Dim cmd As MySQLCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim result As ArrayList
      Dim w As New Wallpaper(Me)

      sqls = "SELECT * FROM skins WHERE s_id = @wall_id"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New MySQLParameter("wall_id", MySQLDbType.Int32)).Value = wallpaper_id

      result = Utils.GetArrayListFromReader(cmd.ExecuteReader(), Utils.eColumnCaseDefinition.ToLower)

      For Each row As Hashtable In result
        w.Datum = row("s_date")
        w.Favorite = row("s_favorite")
        w.Filename = row("s_filename")
        w.FullName = row("s_true_name")
        w.Gesetzt = row("s_set")
        w.ID = row("s_id")
        w.Name = row("s_name")
        w.OrgaID = row("s_organr")
        w.Resolution = row("s_resolution")
      Next

      Return w

    End Get
  End Property



  Public Overrides ReadOnly Property IsFavoritePic(ByVal w As Wallpaper) As Boolean
    Get
      Dim cmd As MySQLCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As MySQLDataAdapter
      Dim ds As New DataSet

      sqls = "SELECT case s_favorite when 'Y' then 'true' else 'false' end isfavpic FROM skins WHERE s_id = @id"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New MySQLParameter("id", MySQLDbType.Int32)).Value = w.ID

      da = New MySQLDataAdapter(cmd)
      da.Fill(ds)

      Return Boolean.Parse(ds.Tables(0).Rows(0).Item("isfavpic"))
    End Get
  End Property

  Public Overrides ReadOnly Property IsFavoriteModel(ByVal w As Wallpaper) As Boolean
    Get
      Dim cmd As MySQLCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As MySQLDataAdapter
      Dim ds As New DataSet

      sqls = "SELECT case count(*) when 0 then 'false' else 'true' end isfavmod FROM skins_favorites WHERE s_name = @sname"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New MySQLParameter("sname", MySQLDbType.VarChar)).Value = w.Name

      da = New MySQLDataAdapter(cmd)
      da.Fill(ds)

      Return Boolean.Parse(ds.Tables(0).Rows(0).Item("isfavmod"))
    End Get
  End Property

  Public Overrides Sub SetFavoriteModel(ByRef w As Wallpaper)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "insert into skins_favorites(s_name) values(@sname)"
    cmd.CommandText = sqls

    cmd.Parameters.Add(New MySQLParameter("sname", MySQLDbType.VarChar)).Value = w.Name

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub SetFavoritePic(ByRef w As Wallpaper)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "update skins set s_favorite = 'Y' where s_id = @id"
    w.Favorite = True
    cmd.CommandText = sqls

    cmd.Parameters.Add(New MySQLParameter("id", MySQLDbType.Int32)).Value = w.ID

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub RemoveFavoriteModel(ByRef w As Wallpaper)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "delete from skins_favorites where s_name = @sname"
    cmd.CommandText = sqls

    cmd.Parameters.Add(New MySQLParameter("sname", MySQLDbType.VarChar)).Value = w.Name

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub RemoveFavoritePic(ByRef w As Wallpaper)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "update skins set s_favorite = null where s_id = @id"
    w.Favorite = False
    cmd.CommandText = sqls

    cmd.Parameters.Add(New MySQLParameter("id", MySQLDbType.Int32)).Value = w.ID

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides ReadOnly Property GetNewDownloadableWallpaper() As DownloadabeWallpaper
    Get
      Dim cmd As MySQLCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As MySQLDataAdapter
      Dim ds As New DataSet
      Dim dw As New DownloadabeWallpaper(me)

      sqls = "select f_skins_nextval() newid "

      cmd.CommandText = sqls

      da = New MySQLDataAdapter(cmd)
      da.Fill(ds)

      dw.ID = ds.Tables(0).Rows(0).Item("newid")

      cmd.Dispose()

      Return dw
    End Get
  End Property

  Public Overrides Sub InsertNewWallpaper(ByRef value As Wallpaper)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "INSERT INTO skins(s_filename, s_date, s_organr, s_name, s_true_name, s_resolution, s_id )"
    sqls &= vbNewLine & " VALUES( @filename, now(), @organr, @sname, initcap(replace(@sname, '-', ' ')), @resolution, @id ) "

    cmd.CommandText = sqls

    cmd.Parameters.Add(New MySQLParameter("filename", MySQLDbType.VarChar)).Value = value.Filename
    cmd.Parameters.Add(New MySQLParameter("organr", MySQLDbType.Int32)).Value = value.OrgaID
    cmd.Parameters.Add(New MySQLParameter("sname", MySQLDbType.VarChar)).Value = value.Name
    cmd.Parameters.Add(New MySQLParameter("resolution", MySQLDbType.VarChar)).Value = value.Resolution
    cmd.Parameters.Add(New MySQLParameter("id", MySQLDbType.Int32)).Value = value.ID

    cmd.ExecuteNonQuery()

    cmd.Dispose()

    value = GetWallpaperById(value.ID)

  End Sub

  Public Overrides ReadOnly Property GetMainList(Optional ByVal filter As String = "", Optional ByVal filtername As String = "Filter") As ListInfoWallpaper()
    Get
      Dim cmd As MySQLCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim liw() As ListInfoWallpaper
      Dim result As ArrayList

      sqls = " select * from ( "

      If filter <> "" Then
        sqls &= vbNewLine & "select -1 id, @user_filtername main_key, s_id, s_date, s_true_name, s_filename, s_resolution, concat(s_true_name , ' (', s_filename , ')') display, 'false' namesort  "
        sqls &= vbNewLine & " from skins "
        sqls &= vbNewLine & " where s_id is not null "
        sqls &= vbNewLine & " and s_filename is not null"
        sqls &= vbNewLine & " and s_filename like concat('%',lower(@user_filter),'%') "
        sqls &= vbNewLine & " union"
        cmd.Parameters.Add(New MySQLParameter("user_filter", MySQLDbType.VarChar)).Value = filter
        cmd.Parameters.Add(New MySQLParameter("user_filtername", MySQLDbType.VarChar)).Value = filtername
      End If
      sqls &= vbNewLine & _config.ÜbersichtSQLMySQL
      sqls &= vbNewLine & " ) t "
      sqls &= vbNewLine & " where s_resolution like @resolution "
      sqls &= vbNewLine & " order by id, s_true_name, s_date"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New MySQLParameter("resolution", MySQLDbType.VarChar)).Value = _config.Resolution

      result = Utils.GetArrayListFromReader(cmd.ExecuteReader(), Utils.eColumnCaseDefinition.ToLower)

      If result.Count > 0 Then
        ReDim liw(result.Count - 1)
        Dim i As Integer = 0
        For Each row As Hashtable In result
          liw(i) = New ListInfoWallpaper
          With liw(i)
            .SortName = row("main_key")
            .SortNumber = row("id")
            .ID = row("s_id")
            .Datum = row("s_date")
            .FullName = row("s_true_name")
            .FileName = row("s_filename")
            .Display = row("display")
            .NameSort = Boolean.Parse(row("namesort"))
          End With
          i += 1
        Next
      Else
        ReDim liw(-1)
      End If
      Return liw
    End Get
  End Property

  Public Overrides Sub DeleteWallpaper(w As Wallpaper, ByVal filesystem As Boolean)
    Dim cmd As MySQLCommand = _connection.CreateCommand()
    Dim sqls As String 

    sqls = "DELETE FROM skins WHERE s_filename = @sfilename"

    cmd.CommandText = sqls 

    cmd.Parameters.Add(New MySQLParameter("sfilename", MySQLDbType.VarChar)).Value = w.Filename 

    cmd.ExecuteNonQuery()

    MyBase.DeleteFromFilesystem(_config.SkinsPath & w.Filename)

  End Sub

  Public Overrides sub UpdateWallpaper(w As Wallpaper)
    ' TODO: UpdateWallpaper ins MySQL Service
  	Throw New NotImplementedException("Diese Funktion ist hier nicht implementiert.")
  End Sub

  Public overrides Function GetWallpaperByName(name As String) As List(of Wallpaper)
    ' TODO: GetWallpaperByName ins MySQL Service
    Throw New NotImplementedException("Diese Funktion ist hier nicht implementiert.")
  End Function

  Public Overrides ReadOnly Property GetListOfWallpaper(ByVal ids As list(of Integer)) As System.Collections.Generic.List(Of Wallpaper)
    Get
      Return New List(Of Wallpaper)
    End Get
  End Property
  
  Public Overrides ReadOnly Property GetModelDetails() As List(Of ModelDetail)
  	get
  		Throw New NotImplementedException("Diese Funktion ist hier nicht implementiert.")
  	end get
  End Property
End Class
